package xxxx.com.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import xxxx.com.annotation.Table;
import xxxx.com.constant.Const;
import xxxx.com.dao.base.BaseDao;
import xxxx.com.model.UserInfo;
import xxxx.com.utils.JDBCUtil;
import xxxx.com.utils.RefUtil;
import xxxx.com.utils.StrUtil;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserInfoDao extends BaseDao<UserInfo> {

    /**
     * 添加注册信息进入user_info表
     *
     * @param userInfo
     */
    public void addUserInfo(UserInfo userInfo) {
        Class<? extends UserInfo> aClass = userInfo.getClass();
        Table annotation = aClass.getAnnotation(Table.class);
        StringBuilder sb = new StringBuilder();
        Object[] params = RefUtil.getParams(userInfo);
        if (null != annotation) {
            String tableName = annotation.value();
            sb.append("insert into ").append(tableName).append(" value (");
            Field[] fields = aClass.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                sb.append("?,");
                if (i == fields.length - 1) {
                    sb.replace(sb.length() - 1, sb.length(), ");");
                }
            }
        }
        adu(sb.toString(), params);
    }

    /**
     * 用户登录时，更新登录时间
     *
     * @param userInfo
     */
    public void updateLoginTime(UserInfo userInfo) {
        Class<? extends UserInfo> aClass = userInfo.getClass();
        Table annotation = aClass.getAnnotation(Table.class);
        String sql = null;
        if (null != annotation) {
            String tableName = annotation.value();
            sql = "update " + tableName + " set login_time = ? where user_id = ?";
        }
        Object[] obj = {userInfo.getLoginTime(), userInfo.getUserId()};
        adu(sql, obj);
    }

    /**
     * 查询用户详细信息，保存在Map集合内
     *
     * @param id
     * @return userDetail
     */
    public Map<String, Object> getUserDetail(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> userDetail = null;
        String sql = "select ui.user_id as userId,ui.real_name as realName,ui.age,ui.gender,u.is_secret as isSecret,\n" +
                "ui.phone,ui.`describe` as introduce,ui.register_time as registerTime,ui.login_time as loginTime,\n" +
                "ui.pic,ui.look,u.dept_id as deptId,d.`name` as deptName,\n" +
                "(select count(*) as total from user_focus where user_focus_id = ui.user_id) as follows\n" +
                "from user_info ui\n" +
                "left join `user` u on u.id = ui.user_id\n" +
                "left join dept d on d.id = u.dept_id\n" +
                "where ui.user_id = ?;";
        try {
            userDetail = qr.query(sql, new MapHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userDetail;
    }


    /**
     * 更新头像图片的路径
     *
     * @param id
     * @param path
     */
    public void updatePic(Integer id, String path) {
        QueryRunner qr = new QueryRunner((JDBCUtil.getDataSource()));
        String sql = "update user_info set pic = ? where user_id = ?;";
        Object[] obj = {path, id};
        adu(sql, obj);
    }

    /**
     * 更新用户的基本信息
     *
     * @param obj
     */
    public void updateUserInfo(Object[] obj) {
        String sql = "update user_info set real_name = ?,age = ?,gender = ?,phone = ? where user_id = ?";
        adu(sql, obj);
    }

    /**
     * 查找用户列表
     *
     * @param uId
     * @param search
     * @param page
     * @return
     */
    public List<Map<String, Object>> findUserList(Integer uId, String search, int page) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id,u.username,ui.real_name as realName,ui.age,ui.gender,ui.describe,uf.id as ufId ");
            sql.append("from user_info ui ");
            sql.append("left join user u on ui.user_id = u.id ");
            sql.append("left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? ");
            if (StrUtil.checkString(search)) {
                sql.append("where ui.real_name like ? or u.username like ? ");
                sql.append("order by u.id limit ?,? ;");
                search = search + "%";
                list = qr.query(sql.toString(), new MapListHandler(), uId, search, search, (page - 1) * Const.MAX_USER_PAGE_SIZE, Const.MAX_USER_PAGE_SIZE);
            } else {
                sql.append("order by u.id limit ?,? ;");
                list = qr.query(sql.toString(), new MapListHandler(), uId, (page - 1) * Const.MAX_USER_PAGE_SIZE, Const.MAX_USER_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 统计用户的个数
     * @param search
     * @return
     */
    public long totalUser(String search) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) as total from user_info ui left join user u on ui.user_id = u.id");
            if (StrUtil.checkString(search)) {
                sql.append(" where ui.real_name like ? or u.username like ?;");
                search = search + "%";
                total = qr.query(sql.toString(), new ScalarHandler<Long>(), search, search);
            } else {
                total = qr.query(sql.toString(), new ScalarHandler<Long>());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    /**
     * 用户的look列+1（被查看次数+1）
     * @param id
     * @param times
     */
    public void addLookCount(Integer id,Integer times) {
        String sql = "update user_info set look = ? where user_id = ?;";
        Object[] obj = {times,id};
        adu(sql,obj);
    }

    /**
     * 查找全体员工以及各自的部门
     * @return
     */
    public List<Map<String, Object>> findUserDeptList() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select ui.user_id as userId,u.username,ui.real_name as realName,u.dept_id as deptId\n" +
                "from user_info ui left join user u on ui.user_id = u.id\n" +
                "left join dept d on u.dept_id = d.id\n" +
                "order by u.dept_id;";
        List<Map<String,Object>> list = null;
        try{
            list = qr.query(sql,new MapListHandler());
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }
}

